package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.maszy.bean.Breakfast;
import com.maszy.bean.GoodPlan;
import com.maszy.bean.Print;
import com.maszy.bean.Product;
import com.maszy.db.DBConnection;

public class GoodPlanService {
	Product service = new Product();
	private static PreparedStatement pst;
	private static ResultSet rs = null;

	public Long add(GoodPlan obj) {
		DBConnection con = new DBConnection();
		String sql = "INSERT INTO [Maszy_GoodPlan]([Maszy_DoorStore_Id],[Maszy_GoodPlan_Type], [Maszy_GoodPlan_date], [Maszy_GoodPlan_Status], [Maszy_GoodPlan_User]) VALUES(?,?,?,?,?) SELECT @@IDENTITY AS GoodPlan_Id";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setLong(1, obj.getDoorStore_Id());
			pst.setString(2, obj.getGoodPlan_Type());
			pst.setString(3, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(Calendar.getInstance().getTime()));
			pst.setInt(4, obj.getGoodPlan_Status());
			pst.setString(5, obj.getGoodPlan_User());
			rs = pst.executeQuery();
			if (rs.next()) {
				obj.setGoodPlan_Id(rs.getLong(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj.getGoodPlan_Id();
	}

	public int addPlanBody(GoodPlan obj) {
		DBConnection con = new DBConnection();
		String sql = "INSERT INTO [Maszy_PlanBody]([Maszy_GoodPlan_Id],[Maszy_Product_Id],[Maszy_Product_Name],[Maszy_Product_Specification],[Maszy_Product_Unit],[Maszy_Product_Quantity],Maszy_Product_Dept_Name,[Maszy_PlanBody_Remark]) VALUES(?,?,?,?,?,?,?,?)";
		int i = 0;
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setLong(1, obj.getGoodPlan_Id());
			pst.setLong(2, obj.getProduct().getProduct_Id());
			pst.setString(3, obj.getProduct().getProduct_Name());
			pst.setString(4, obj.getProduct().getProduct_Specification());
			pst.setString(5, obj.getProduct().getProduct_Unit());
			pst.setString(6, obj.getProduct().getProduct_Quantity());
			pst.setString(7, obj.getProduct().getProduct_Dept_Name());
			pst.setString(8, obj.getProduct().getProduct_Remark());
			i = pst.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}

	public GoodPlan PrintGoodPlan(GoodPlan goodplan) {
		DBConnection con = new DBConnection();
		GoodPlan obj = new GoodPlan();
		String sql = "select a.*,b.maszy_doorstore_name from maszy_goodplan as a left join maszy_doorstore as b on a.maszy_doorstore_id=b.maszy_doorstore_id where a.maszy_goodplan_id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setLong(1, goodplan.getGoodPlan_Id());
			rs = pst.executeQuery();
			while (rs.next()) {
				obj.setGoodPlan_Id(goodplan.getGoodPlan_Id());
				obj.setDoorStore_Id(rs.getLong("Maszy_DoorStore_Id"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_date(rs.getString("Maszy_GoodPlan_date"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_User(rs.getString("Maszy_GoodPlan_User"));
				obj.setGoodPlan_Remark(rs.getString("Maszy_GoodPlan_Remark"));
				obj.setDoorStore_Name(rs.getString("Maszy_DoorStore_Name"));

			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}

		con = new DBConnection();
		String sql2 = "select * from maszy_planbody where maszy_goodplan_id=? order by maszy_product_dept_name";
		try {
			pst = con.getConnection().prepareStatement(sql2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setLong(1, goodplan.getGoodPlan_Id());
			rs = pst.executeQuery();
			List<Product> productList = new ArrayList<Product>();
			while (rs.next()) {
				Product product = new Product();
				product.setProduct_Id(rs.getLong("Maszy_Product_Id"));
				product.setProduct_Name(rs.getString("Maszy_Product_Name"));
				product.setProduct_Specification(rs
						.getString("Maszy_Product_Specification"));
				product.setProduct_Unit(rs.getString("Maszy_Product_Unit"));
				product.setProduct_Quantity(rs
						.getString("Maszy_Product_Quantity"));
				product.setProduct_Dept_Name(rs
						.getString("Maszy_Product_Dept_Name"));
				product.setProduct_Remark(rs.getString("Maszy_PlanBody_Remark"));
				productList.add(product);
			}
			obj.setProductList(productList);

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}

		return obj;
	}

	public int delete(Product obj) {
		DBConnection con = new DBConnection();
		int i = 0;
		String sql = "delete from  Maszy_Product  where Maszy_Product_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setLong(1, obj.getProduct_Id());
			i = pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}

	public Product queryById(Product obj) {
		DBConnection con = new DBConnection();
		String sql = "select * from Maszy_Product where Maszy_Product_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setLong(1, obj.getProduct_Id());
			rs = pst.executeQuery();
			while (rs.next()) {
				obj = new Product();
				obj.setProduct_Id(rs.getLong("Maszy_Product_Id"));
				obj.setProduct_Name(rs.getString("Maszy_Product_Name"));
				obj.setProduct_Unit(rs.getString("Maszy_Product_Unit"));
				obj.setProduct_Remark(rs.getString("Maszy_Product_Remark"));
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}

	public List<GoodPlan> queryAll(GoodPlan obj) {
		List<GoodPlan> list = new ArrayList<GoodPlan>();
		DBConnection con = new DBConnection();
		String sql = "select a.*,b.maszy_doorstore_name from maszy_goodplan as a left join maszy_doorstore as b on a.maszy_doorstore_id=b.maszy_doorstore_id where a.maszy_goodplan_date between ? and ? ";
		if (Long.parseLong("0") < obj.getDoorStore_Id()) {
			sql = sql + " and a.maszy_doorstore_id=?";
		}
		if (!"0".equals(obj.getGoodPlan_Type())) {
			sql = sql + " and a.maszy_goodplan_type=?";
		}
		sql=sql+" order by a.maszy_goodplan_date desc";
		try {
			int i = 1;
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(i, obj.getGoodPlan_BeginDate() + " 00:00:00");
			i++;
			pst.setString(i, obj.getGoodPlan_BeginDate() + " 23:59:59");

			if (Long.parseLong("0") < obj.getDoorStore_Id()) {
				i++;
				pst.setLong(i, obj.getDoorStore_Id());
			}
			if (!"0".equals(obj.getGoodPlan_Type())) {
				i++;
				pst.setString(i, obj.getGoodPlan_Type());
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				obj = new GoodPlan();
				obj.setGoodPlan_Id(rs.getLong("Maszy_GoodPlan_Id"));
				obj.setDoorStore_Id(rs.getLong("Maszy_DoorStore_Id"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_date(rs.getString("Maszy_GoodPlan_date"));
				obj.setGoodPlan_Status(rs.getInt("Maszy_GoodPlan_Status"));
				obj.setGoodPlan_User(rs.getString("Maszy_GoodPlan_User"));
				obj.setGoodPlan_Remark(rs.getString("Maszy_GoodPlan_Remark"));
				obj.setDoorStore_Name(rs.getString("Maszy_DoorStore_Name"));
				list.add(obj);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}

	// 过滤一下.将查询数据按表头表体转换下.
	public GoodPlan changeGoodPlan(GoodPlan obj) {
		List<GoodPlan> goodPlanList = new ArrayList<GoodPlan>();
		List<Product> tempList = new ArrayList<Product>();
		String dept_name = "";
		for (int j = 0; j < obj.getProductList().size(); j++) {
			if (j == 0) {
				dept_name = obj.getProductList().get(j).getProduct_Dept_Name();
				tempList.add(obj.getProductList().get(j));
			} else {
				if (dept_name.equals(obj.getProductList().get(j)
						.getProduct_Dept_Name())) {
					tempList.add(obj.getProductList().get(j));
				} else {
					GoodPlan goodPlan = new GoodPlan();
					goodPlan.setDept_Name(dept_name);
					goodPlan.setProductList(tempList);
					goodPlanList.add(goodPlan);
					tempList = new ArrayList<Product>();
					tempList.add(obj.getProductList().get(j));
					dept_name = obj.getProductList().get(j)
							.getProduct_Dept_Name();
				}
			}
			if (j == obj.getProductList().size() - 1) {
				GoodPlan goodPlan = new GoodPlan();
				goodPlan.setDept_Name(dept_name);
				goodPlan.setProductList(tempList);
				goodPlanList.add(goodPlan);
			}
		}
		obj.setGoodPlanList(goodPlanList);
		return obj;
	}

	public Print queryByDeptName(String str) {
		Print obj = new Print();
		DBConnection con = new DBConnection();
		String sql = "select a.* from Maszy_Print a,maszy_dept b where a.print_id=b.print_id and b.dept_name=?";
		try {

			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, str);

			rs = pst.executeQuery();
			while (rs.next()) {
				obj.setPrint_id(rs.getInt("print_id"));
				obj.setPrint_name(rs.getString("print_name"));
				obj.setPrint_ip(rs.getString("print_ip"));
				obj.setRemark(rs.getString("print_remark"));
			}
		} catch (Exception e) {
			System.out.println(e.getMessage() + "???????????????????");
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}

	public List<GoodPlan> queryOneDayGoodPlan1(GoodPlan goodPlan) {
		// 原材料供应单表头

		List<GoodPlan> list = new ArrayList<GoodPlan>();
		DBConnection con = new DBConnection();
		String sql = "SELECT * FROM MASZY_GOODPLAN WHERE MASZY_GOODPLAN_DATE BETWEEN  ? AND ? AND MASZY_GOODPLAN_TYPE=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, goodPlan.getGoodPlan_BeginDate());
			pst.setString(2, goodPlan.getGoodPlan_EndDate());
			pst.setString(3, goodPlan.getGoodPlan_Type());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = pst.executeQuery();
			while (rs.next()) {
				GoodPlan obj = new GoodPlan();
				obj.setGoodPlan_Id(rs.getLong("Maszy_GoodPlan_id"));
				obj.setDoorStore_Id(rs.getLong("Maszy_DoorStore_Id"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_date(rs.getString("Maszy_GoodPlan_date"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_User(rs.getString("Maszy_GoodPlan_User"));
				obj.setGoodPlan_Remark(rs.getString("Maszy_GoodPlan_Remark"));
				list.add(obj);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		// 获取具体要货详细
		if (list != null && list.size() > 0) {
			if (list.get(0).getGoodPlan_Id() != null) {
				for (int i = 0; i < list.size(); i++) {
					String sql2 = "SELECT * FROM MASZY_PLANBODY WHERE MASZY_GOODPLAN_ID=?";
					con = new DBConnection();
					try {
						pst = con.getConnection().prepareStatement(sql2);
						pst.setLong(1, list.get(i).getGoodPlan_Id());
						rs = pst.executeQuery();
						List<Product> productList = new ArrayList<Product>();
						while (rs.next()) {
							Product product = new Product();
							product.setProduct_Id(rs
									.getLong("Maszy_Product_Id"));
							product.setProduct_Name(rs
									.getString("Maszy_Product_Name"));
							product.setProduct_Specification(rs
									.getString("Maszy_Product_Specification"));
							product.setProduct_Unit(rs
									.getString("Maszy_Product_Unit"));
							product.setProduct_Quantity(rs
									.getString("Maszy_Product_Quantity"));
							product.setProduct_Dept_Name(rs
									.getString("Maszy_Product_Dept_Name"));
							product.setProduct_Remark(rs
									.getString("Maszy_PlanBody_Remark"));
							productList.add(product);
						}
						list.get(i).setProductList(productList);
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							if (rs != null) {
								rs.close();
								rs = null;
							}
							if (con != null) {
								con.close();
								con = null;
							}
						} catch (Exception e) {
							// TODO 自动生成 catch 块
							e.printStackTrace();
						}
					}
				}
			}
		}
		return list;
	}
	public List<GoodPlan> queryOneDayGoodPlan2(GoodPlan goodPlan) {
		// 原材料供应单表头

		List<GoodPlan> list = new ArrayList<GoodPlan>();
		DBConnection con = new DBConnection();
		String sql = "SELECT * FROM MASZY_GOODPLAN WHERE MASZY_GOODPLAN_DATE BETWEEN  ? AND ? AND MASZY_GOODPLAN_TYPE=?";
//		System.out.println(goodPlan.getGoodPlan_BeginDate()+"\t"+goodPlan.getGoodPlan_EndDate()+"\t"+goodPlan.getGoodPlan_Type());
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, goodPlan.getGoodPlan_BeginDate());
			pst.setString(2, goodPlan.getGoodPlan_EndDate());
			pst.setString(3, goodPlan.getGoodPlan_Type());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = pst.executeQuery();
			while (rs.next()) {
				GoodPlan obj = new GoodPlan();
				obj.setGoodPlan_Id(rs.getLong("Maszy_GoodPlan_id"));
				obj.setDoorStore_Id(rs.getLong("Maszy_DoorStore_Id"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_date(rs.getString("Maszy_GoodPlan_date"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_User(rs.getString("Maszy_GoodPlan_User"));
				obj.setGoodPlan_Remark(rs.getString("Maszy_GoodPlan_Remark"));
				list.add(obj);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		// 获取具体要货详细
		if (list != null && list.size() > 0) {
			if (list.get(0).getGoodPlan_Id() != null) {
				for (int i = 0; i < list.size(); i++) {
					String sql2 = "SELECT * FROM MASZY_PLANBODY WHERE MASZY_GOODPLAN_ID=?";
					con = new DBConnection();
					try {
						pst = con.getConnection().prepareStatement(sql2);
						pst.setLong(1, list.get(i).getGoodPlan_Id());
						rs = pst.executeQuery();
						List<Breakfast> breakfastList = new ArrayList<Breakfast>();
						while (rs.next()) {
							Breakfast breakfast = new Breakfast();
							breakfast.setBreakfast_id(rs
									.getLong("Maszy_Product_Id"));
							breakfast.setProduct_name(rs
									.getString("Maszy_Product_Name"));
							breakfast.setSpecification(rs
									.getString("Maszy_Product_Specification"));
							breakfast.setUnit(rs
									.getString("Maszy_Product_Unit"));
							breakfast.setQuantity(rs
									.getInt("Maszy_Product_Quantity"));
							breakfast.setDept_name(rs
									.getString("Maszy_Product_Dept_Name"));
							breakfast.setRemark(rs
									.getString("Maszy_PlanBody_Remark"));
							breakfastList.add(breakfast);
						}
						list.get(i).setBreakfastList(breakfastList);
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							if (rs != null) {
								rs.close();
								rs = null;
							}
							if (con != null) {
								con.close();
								con = null;
							}
						} catch (Exception e) {
							// TODO 自动生成 catch 块
							e.printStackTrace();
						}
					}
				}
			}
		}
		return list;
	}
	public List<GoodPlan> queryOneDayGoodPlanByDeptName(GoodPlan goodPlan) {
		// 原材料供应单表头

		List<GoodPlan> list = new ArrayList<GoodPlan>();
		DBConnection con = new DBConnection();
		String sql = "select a.*,b.Maszy_DoorStore_Name  from maszy_goodplan a left join maszy_doorstore b on a.maszy_doorstore_id =b.maszy_doorstore_id WHERE a.MASZY_GOODPLAN_DATE BETWEEN  ? AND ? AND a.MASZY_GOODPLAN_TYPE=? ";
//		System.out.println(goodPlan.getGoodPlan_BeginDate()+"\t"+goodPlan.getGoodPlan_EndDate()+"\t"+goodPlan.getGoodPlan_Type());
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, goodPlan.getGoodPlan_BeginDate());
			pst.setString(2, goodPlan.getGoodPlan_EndDate());
			pst.setString(3, goodPlan.getGoodPlan_Type());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = pst.executeQuery();
			while (rs.next()) {
				GoodPlan obj = new GoodPlan();
				obj.setGoodPlan_Id(rs.getLong("Maszy_GoodPlan_id"));
				obj.setDoorStore_Id(rs.getLong("Maszy_DoorStore_Id"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_date(rs.getString("Maszy_GoodPlan_date"));
				obj.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				obj.setGoodPlan_User(rs.getString("Maszy_GoodPlan_User"));
				obj.setGoodPlan_Remark(rs.getString("Maszy_GoodPlan_Remark"));
				obj.setDoorStore_Name(rs.getString("Maszy_DoorStore_Name"));
				list.add(obj);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		// 获取具体要货详细
		if (list != null && list.size() > 0) {
			if (list.get(0).getGoodPlan_Id() != null) {
				for (int i = 0; i < list.size(); i++) {
					String sql2 = "SELECT * FROM MASZY_PLANBODY WHERE MASZY_GOODPLAN_ID=? and Maszy_Product_Dept_Name=?";
					con = new DBConnection();
					try {
						pst = con.getConnection().prepareStatement(sql2);
						pst.setLong(1, list.get(i).getGoodPlan_Id());
						pst.setString(2, goodPlan.getDept_Name());
						rs = pst.executeQuery();
						List<Product> productList = new ArrayList<Product>();
						while (rs.next()) {
							Product product = new Product();
							product.setProduct_Id(rs.getLong("Maszy_Product_Id"));
							product.setProduct_Name(rs.getString("Maszy_Product_Name"));
							product.setProduct_Specification(rs
									.getString("Maszy_Product_Specification"));
							product.setProduct_Unit(rs.getString("Maszy_Product_Unit"));
							product.setProduct_Quantity(rs
									.getString("Maszy_Product_Quantity"));
							product.setProduct_Dept_Name(rs
									.getString("Maszy_Product_Dept_Name"));
							product.setProduct_Remark(rs.getString("Maszy_PlanBody_Remark"));
							productList.add(product);
						}
						list.get(i).setProductList(productList);
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							if (rs != null) {
								rs.close();
								rs = null;
							}
							if (con != null) {
								con.close();
								con = null;
							}
						} catch (Exception e) {
							// TODO 自动生成 catch 块
							e.printStackTrace();
						}
					}
				}
			}
		}
		return list;
	}
	public GoodPlan queryGoodPlanByDoorStoreAndType(GoodPlan goodplan) {
		DBConnection con = new DBConnection();
		String sql = "SELECT top 1 * FROM MASZY_GOODPLAN WHERE MASZY_GOODPLAN_DATE BETWEEN  ? AND ? AND MASZY_GOODPLAN_TYPE=? AND Maszy_DoorStore_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, goodplan.getGoodPlan_BeginDate());
			pst.setString(2, goodplan.getGoodPlan_EndDate());
			pst.setString(3, goodplan.getGoodPlan_Type());
			pst.setLong(4, goodplan.getDoorStore_Id());
			rs = pst.executeQuery();
			while (rs.next()) {
				goodplan.setGoodPlan_Id(rs.getLong("Maszy_GoodPlan_id"));
				goodplan.setDoorStore_Id(rs.getLong("Maszy_DoorStore_Id"));
				goodplan.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				goodplan.setGoodPlan_date(rs.getString("Maszy_GoodPlan_date"));
				goodplan.setGoodPlan_Type(rs.getString("Maszy_GoodPlan_Type"));
				goodplan.setGoodPlan_User(rs.getString("Maszy_GoodPlan_User"));
				goodplan.setGoodPlan_Remark(rs.getString("Maszy_GoodPlan_Remark"));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return goodplan;
	}
	public GoodPlan queryPlanBodyByGoodPlanId(GoodPlan obj){
		DBConnection con = new DBConnection();
		String sql2 = "select * from maszy_planbody where maszy_goodplan_id=?";
		try {
			pst = con.getConnection().prepareStatement(sql2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setLong(1, obj.getGoodPlan_Id());
			rs = pst.executeQuery();
			List<Product> productList = new ArrayList<Product>();
			while (rs.next()) {
				Product product = new Product();
				product.setProduct_Id(rs.getLong("Maszy_Product_Id"));
				product.setProduct_Name(rs.getString("Maszy_Product_Name"));
				product.setProduct_Specification(rs
						.getString("Maszy_Product_Specification"));
				product.setProduct_Unit(rs.getString("Maszy_Product_Unit"));
				product.setProduct_Quantity(rs
						.getString("Maszy_Product_Quantity"));
				product.setProduct_Dept_Name(rs
						.getString("Maszy_Product_Dept_Name"));
				product.setProduct_Remark(rs.getString("Maszy_PlanBody_Remark"));
				productList.add(product);
			}
			obj.setProductList(productList);

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}

		return obj;
	}
	public Long deletePlanBodyByGoodPlanId(GoodPlan obj) {
		DBConnection con = new DBConnection();
		int i = 0;
		String sql = "delete from  maszy_planbody  where maszy_goodplan_id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setLong(1, obj.getGoodPlan_Id());
			i = pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj.getGoodPlan_Id();
		
	}
}
